package com.ss.summer.sql.statics;

/**
 * @Author: 夏十七
 * @Date: 2019/9/22 17:41
 * @Version 1.0
 */
public interface SummerSqlConstant {

    /**
     * 待审核
     */
    String FIND_WAIT_AUDIT = "SELECT  " +
            "             ins.*  " +
            "             {fi}  " +
            "            FROM  " +
            "                summer_instance ins  " +
            "                INNER JOIN summer_node AS node ON ins.now_node = node.id  " +
            "                INNER JOIN summer_flow AS flow ON flow.id = ins.flow_id  " +
            "                INNER JOIN summer_node_member AS nm ON nm.node_id = node.id  " +
            "                INNER JOIN {join}  " +
            "            WHERE  " +
            "                ins.`status` = 1  " +
            "                AND nm.is_user = 1  " +
            "                AND flow.api_path = ?  " +
            "                AND nm.member_id = ?  " +
            "                AND (  " +
            "                    ( SELECT count( * ) FROM summer_appoint_member AS am WHERE am.node_id = node.id AND am.instance_id = ins.id ) = 0  " +
            "                    OR nm.member_id IN (  " +
            "                        ( SELECT am.member_id FROM summer_appoint_member AS am WHERE am.node_id = node.id AND am.instance_id = ins.id )  " +
            "                    )  " +
            "                )" +
            "               AND (  " +
            "                    (SELECT count(*) FROM summer_appoint_member_global AS amg WHERE amg.instance_id = ins.id) = 0  " +
            "                OR nm.member_id IN (  " +
            "                    SELECT amg.member_id FROM summer_appoint_member_global AS amg WHERE amg.instance_id = ins.id  " +
            "                )) {where}" ;

    String FIND_WAIT_AUDIT_COUNT = "SELECT count(*)  " +
            "            FROM  " +
            "                summer_instance ins  " +
            "                INNER JOIN summer_node AS node ON ins.now_node = node.id  " +
            "                INNER JOIN summer_flow AS flow ON flow.id = ins.flow_id  " +
            "                INNER JOIN summer_node_member AS nm ON nm.node_id = node.id  " +
            "                INNER JOIN {join}  " +
            "            WHERE  " +
            "                ins.`status` = 1  " +
            "                AND nm.is_user = 1  " +
            "                AND flow.api_path = ?  " +
            "                AND nm.member_id = ?  " +
            "                AND (  " +
            "                    ( SELECT count( * ) FROM summer_appoint_member AS am WHERE am.node_id = node.id AND am.instance_id = ins.id ) = 0  " +
            "                    OR nm.member_id IN (  " +
            "                        ( SELECT am.member_id FROM summer_appoint_member AS am WHERE am.node_id = node.id AND am.instance_id = ins.id )  " +
            "                    )  " +
            "                )" +
            "               AND (  " +
            "                    (SELECT count(*) FROM summer_appoint_member_global AS amg WHERE amg.instance_id = ins.id) = 0  " +
            "                OR nm.member_id IN (  " +
            "                    SELECT amg.member_id FROM summer_appoint_member_global AS amg WHERE amg.instance_id = ins.id  " +
            "                )) {where} " ;

    String FIND_WAIT_AUDIT_PAGE = "SELECT  " +
            "             ins.*  " +
            "             {fi}  " +
            "            FROM  " +
            "                summer_instance ins  " +
            "                INNER JOIN summer_node AS node ON ins.now_node = node.id  " +
            "                INNER JOIN summer_flow AS flow ON flow.id = ins.flow_id  " +
            "                INNER JOIN summer_node_member AS nm ON nm.node_id = node.id  " +
            "                INNER JOIN {join}  " +
            "            WHERE  " +
            "                ins.`status` = 1  " +
            "                AND nm.is_user = 1  " +
            "                AND flow.api_path = ?  " +
            "                AND nm.member_id = ?  " +
            "                AND (  " +
            "                    ( SELECT count( * ) FROM summer_appoint_member AS am WHERE am.node_id = node.id AND am.instance_id = ins.id ) = 0  " +
            "                    OR nm.member_id IN (  " +
            "                        ( SELECT am.member_id FROM summer_appoint_member AS am WHERE am.node_id = node.id AND am.instance_id = ins.id )  " +
            "                    )  " +
            "                )" +
            "               AND (  " +
            "                    (SELECT count(*) FROM summer_appoint_member_global AS amg WHERE amg.instance_id = ins.id) = 0  " +
            "                OR nm.member_id IN (  " +
            "                    SELECT amg.member_id FROM summer_appoint_member_global AS amg WHERE amg.instance_id = ins.id  " +
            "                )) {where}" +
            "               LIMIT {start}, {size} " ;

    /**
     * 已审核
     */
    String FIND_AUDITED = "SELECT " +
            "            DISTINCT ins.* {fi}" +
            "        FROM " +
            "            summer_instance ins " +
            "            INNER JOIN summer_flow AS flow ON flow.id = ins.flow_id  " +
            "            INNER JOIN summer_record sr ON sr.instance_id = ins.id  " +
            "            INNER JOIN {join} " +
            "        WHERE  " +
            "            flow.api_path = ?  " +
            "            AND sr.member_id = ? " +
            "            {where} " ;

    String FIND_AUDITED_PAGE = "SELECT " +
            "            DISTINCT ins.* {fi}" +
            "        FROM " +
            "            summer_instance ins " +
            "            INNER JOIN summer_flow AS flow ON flow.id = ins.flow_id  " +
            "            INNER JOIN summer_record sr ON sr.instance_id = ins.id  " +
            "            INNER JOIN {join} " +
            "        WHERE  " +
            "            flow.api_path = ?  " +
            "            AND sr.member_id = ? " +
            "            {where} LIMIT {start}, {size} " ;

    String FIND_AUDITED_COUNT = "SELECT " +
            "            count(distinct ins.id) " +
            "        FROM " +
            "            summer_instance ins " +
            "            INNER JOIN summer_flow AS flow ON flow.id = ins.flow_id  " +
            "            INNER JOIN summer_record sr ON sr.instance_id = ins.id  " +
            "            INNER JOIN {join} " +
            "        WHERE  " +
            "            flow.api_path = ?  " +
            "            AND sr.member_id = ? " +
            "            {where}  " ;

    /**
     * 有权限的
     */
    String FIND_HAS_AUTH= "" +
            "SELECT  " +
            "        distinct ins.*  {fi} " +
            "        FROM  " +
            "        summer_instance ins  " +
            "        INNER JOIN summer_flow AS flow ON flow.id = ins.flow_id  " +
            "        INNER JOIN summer_node AS node ON ins.flow_id = node.flow_id  " +
            "        INNER JOIN summer_node_member AS nm ON nm.node_id = node.id  " +
            "        INNER JOIN " +
            "        (  " +
            "        SELECT  " +
            "        ins.id as insId,  " +
            "        CASE WHEN MAX( auditedNode.sort ) is NULL THEN 0 ELSE MAX( auditedNode.sort ) END AS maxSort  " +
            "        FROM  " +
            "        summer_instance ins  " +
            "        INNER JOIN summer_flow AS flow ON flow.id = ins.flow_id  " +
            "        INNER JOIN summer_record sr ON sr.instance_id = ins.id  " +
            "        LEFT JOIN summer_node AS auditedNode ON auditedNode.id = sr.node_id  " +
            "        WHERE flow.api_path = ?  " +
            "        GROUP BY ins.id  " +
            "        )  as temp ON temp.insId = ins.id  " +
            "        INNER JOIN {join}" +
            "        WHERE member_id = ?  " +
            "        AND flow.api_path = ?  " +
            "        AND nm.is_use = 1  " +
            "        AND (  " +
            "        ( SELECT count( * ) FROM summer_appoint_member AS am WHERE am.node_id = node.id AND am.instance_id = ins.id ) = 0  " +
            "        OR nm.member_id IN (  " +
            "        ( SELECT am.member_id FROM summer_appoint_member AS am WHERE am.node_id = node.id AND am.instance_id = ins.id )  " +
            "        )  " +
            "        )  " +
            "        AND (  " +
            "        ( SELECT count( * ) FROM summer_appoint_member_global AS amg WHERE amg.instance_id = ins.id ) = 0  " +
            "        OR nm.member_id IN ( SELECT amg.member_id FROM summer_appoint_member_global AS amg WHERE amg.instance_id = ins.id )  " +
            "        )  " +
            "        AND temp.maxSort + 1 &lt;= node.sort " +
            "       {where} ";

    String FIND_HAS_AUTH_PAGE= "" +
            "SELECT  " +
            "        distinct ins.*  {fi} " +
            "        FROM  " +
            "        summer_instance ins  " +
            "        INNER JOIN summer_flow AS flow ON flow.id = ins.flow_id  " +
            "        INNER JOIN summer_node AS node ON ins.flow_id = node.flow_id  " +
            "        INNER JOIN summer_node_member AS nm ON nm.node_id = node.id  " +
            "        INNER JOIN " +
            "        (  " +
            "        SELECT  " +
            "        ins.id as insId,  " +
            "        CASE WHEN MAX( auditedNode.sort ) is NULL THEN 0 ELSE MAX( auditedNode.sort ) END AS maxSort  " +
            "        FROM  " +
            "        summer_instance ins  " +
            "        INNER JOIN summer_flow AS flow ON flow.id = ins.flow_id  " +
            "        INNER JOIN summer_record sr ON sr.instance_id = ins.id  " +
            "        LEFT JOIN summer_node AS auditedNode ON auditedNode.id = sr.node_id  " +
            "        WHERE flow.api_path = ?  " +
            "        GROUP BY ins.id  " +
            "        )  as temp ON temp.insId = ins.id  " +
            "        INNER JOIN {join}" +
            "        WHERE member_id = ?  " +
            "        AND flow.api_path = ?  " +
            "        AND nm.is_use = 1  " +
            "        AND (  " +
            "        ( SELECT count( * ) FROM summer_appoint_member AS am WHERE am.node_id = node.id AND am.instance_id = ins.id ) = 0  " +
            "        OR nm.member_id IN (  " +
            "        ( SELECT am.member_id FROM summer_appoint_member AS am WHERE am.node_id = node.id AND am.instance_id = ins.id )  " +
            "        )  " +
            "        )  " +
            "        AND (  " +
            "        ( SELECT count( * ) FROM summer_appoint_member_global AS amg WHERE amg.instance_id = ins.id ) = 0  " +
            "        OR nm.member_id IN ( SELECT amg.member_id FROM summer_appoint_member_global AS amg WHERE amg.instance_id = ins.id )  " +
            "        )  " +
            "        AND temp.maxSort + 1 &lt;= node.sort " +
            "       {where} LIMIT {start}, {size}";

    String FIND_HAS_AUTH_COUNT= "" +
            "SELECT  " +
            "        count(distinct ins.id) " +
            "        FROM  " +
            "        summer_instance ins  " +
            "        INNER JOIN summer_flow AS flow ON flow.id = ins.flow_id  " +
            "        INNER JOIN summer_node AS node ON ins.flow_id = node.flow_id  " +
            "        INNER JOIN summer_node_member AS nm ON nm.node_id = node.id  " +
            "        INNER JOIN " +
            "        (  " +
            "        SELECT  " +
            "        ins.id as insId,  " +
            "        CASE WHEN MAX( auditedNode.sort ) is NULL THEN 0 ELSE MAX( auditedNode.sort ) END AS maxSort  " +
            "        FROM  " +
            "        summer_instance ins  " +
            "        INNER JOIN summer_flow AS flow ON flow.id = ins.flow_id  " +
            "        INNER JOIN summer_record sr ON sr.instance_id = ins.id  " +
            "        LEFT JOIN summer_node AS auditedNode ON auditedNode.id = sr.node_id  " +
            "        WHERE flow.api_path = ?  " +
            "        GROUP BY ins.id  " +
            "        )  as temp ON temp.insId = ins.id  " +
            "        INNER JOIN {join}" +
            "        WHERE member_id = ?  " +
            "        AND flow.api_path = ?  " +
            "        AND nm.is_use = 1  " +
            "        AND (  " +
            "        ( SELECT count( * ) FROM summer_appoint_member AS am WHERE am.node_id = node.id AND am.instance_id = ins.id ) = 0  " +
            "        OR nm.member_id IN (  " +
            "        ( SELECT am.member_id FROM summer_appoint_member AS am WHERE am.node_id = node.id AND am.instance_id = ins.id )  " +
            "        )  " +
            "        )  " +
            "        AND (  " +
            "        ( SELECT count( * ) FROM summer_appoint_member_global AS amg WHERE amg.instance_id = ins.id ) = 0  " +
            "        OR nm.member_id IN ( SELECT amg.member_id FROM summer_appoint_member_global AS amg WHERE amg.instance_id = ins.id )  " +
            "        )  " +
            "        AND temp.maxSort + 1 &lt;= node.sort " +
            "       {where} ";


    /**
     * 他人审核的
     */
    String FIND_AUDITED_OTHER = "" +
            "SELECT  " +
            "        distinct ins.*  {fi} " +
            "        FROM  " +
            "        summer_instance ins  " +
            "        INNER JOIN summer_flow AS flow ON flow.id = ins.flow_id  " +
            "        INNER JOIN summer_node AS node ON ins.flow_id = node.flow_id  " +
            "        INNER JOIN summer_node_member AS nm ON nm.node_id = node.id  " +
            "        INNER JOIN summer_record sr ON sr.instance_id = ins.id  " +
            "        INNER JOIN summer_node as auditedNode on auditedNode.id = sr.node_id  " +
            "        INNER JOIN {join}" +
            "        WHERE  " +
            "        sr.member_id != ? AND auditedNode.sort &gt; node.sort  " +
            "        AND nm.is_user = 1 AND  flow.api_path = ?  " +
            "        AND (  " +
            "                ( SELECT count( * ) FROM summer_appoint_member AS am WHERE am.node_id = node.id AND am.instance_id = ins.id ) = 0  " +
            "                OR nm.member_id IN (  " +
            "                    ( SELECT am.member_id FROM summer_appoint_member AS am WHERE am.node_id = node.id AND am.instance_id = ins.id )  " +
            "                )  " +
            "            )  " +
            "            AND (  " +
            "                ( SELECT count( * ) FROM summer_appoint_member_global AS amg WHERE amg.instance_id = ins.id ) = 0  " +
            "            OR nm.member_id IN ( SELECT amg.member_id FROM summer_appoint_member_global AS amg WHERE amg.instance_id = ins.id )  " +
            "            ) {where} ";


    String FIND_AUDITED_OTHER_PAGE = "" +
            "SELECT  " +
            "        distinct ins.*  {fi} " +
            "        FROM  " +
            "        summer_instance ins  " +
            "        INNER JOIN summer_flow AS flow ON flow.id = ins.flow_id  " +
            "        INNER JOIN summer_node AS node ON ins.flow_id = node.flow_id  " +
            "        INNER JOIN summer_node_member AS nm ON nm.node_id = node.id  " +
            "        INNER JOIN summer_record sr ON sr.instance_id = ins.id  " +
            "        INNER JOIN summer_node as auditedNode on auditedNode.id = sr.node_id  " +
            "        INNER JOIN {join}" +
            "        WHERE  " +
            "        sr.member_id != ? AND auditedNode.sort &gt; node.sort  " +
            "        AND nm.is_user = 1 AND  flow.api_path = ?  " +
            "        AND (  " +
            "                ( SELECT count( * ) FROM summer_appoint_member AS am WHERE am.node_id = node.id AND am.instance_id = ins.id ) = 0  " +
            "                OR nm.member_id IN (  " +
            "                    ( SELECT am.member_id FROM summer_appoint_member AS am WHERE am.node_id = node.id AND am.instance_id = ins.id )  " +
            "                )  " +
            "            )  " +
            "            AND (  " +
            "                ( SELECT count( * ) FROM summer_appoint_member_global AS amg WHERE amg.instance_id = ins.id ) = 0  " +
            "            OR nm.member_id IN ( SELECT amg.member_id FROM summer_appoint_member_global AS amg WHERE amg.instance_id = ins.id )  " +
            "            ) {where} LIMIT {start}, {size} ";

    String FIND_AUDITED_OTHER_COUNT = "" +
            "SELECT  " +
            "        count(distinct ins.id) " +
            "        FROM  " +
            "        summer_instance ins  " +
            "        INNER JOIN summer_flow AS flow ON flow.id = ins.flow_id  " +
            "        INNER JOIN summer_node AS node ON ins.flow_id = node.flow_id  " +
            "        INNER JOIN summer_node_member AS nm ON nm.node_id = node.id  " +
            "        INNER JOIN summer_record sr ON sr.instance_id = ins.id  " +
            "        INNER JOIN summer_node as auditedNode on auditedNode.id = sr.node_id  " +
            "        INNER JOIN {join}" +
            "        WHERE  " +
            "        sr.member_id != ? AND auditedNode.sort &gt; node.sort  " +
            "        AND nm.is_user = 1 AND  flow.api_path = ?  " +
            "        AND (  " +
            "                ( SELECT count( * ) FROM summer_appoint_member AS am WHERE am.node_id = node.id AND am.instance_id = ins.id ) = 0  " +
            "                OR nm.member_id IN (  " +
            "                    ( SELECT am.member_id FROM summer_appoint_member AS am WHERE am.node_id = node.id AND am.instance_id = ins.id )  " +
            "                )  " +
            "            )  " +
            "            AND (  " +
            "                ( SELECT count( * ) FROM summer_appoint_member_global AS amg WHERE amg.instance_id = ins.id ) = 0  " +
            "            OR nm.member_id IN ( SELECT amg.member_id FROM summer_appoint_member_global AS amg WHERE amg.instance_id = ins.id )  " +
            "            ) {where}  ";

}
